if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MapStagedFacilitiesFromInfoEd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MapStagedFacilitiesFromInfoEd]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



/***************************************************************************
* Name:			MapStagedFacilitiesFromInfoEd
*
***************************************************************************/
CREATE PROCEDURE dbo.MapStagedFacilitiesFromInfoEd
AS
BEGIN

	SET NOCOUNT ON

	-- SvcEntityID	
	UPDATE STG 
		SET STG.SvcEntityId = A.SvcEntityId
		FROM Stg_IE_SvcEntityFacility_Ctl STG JOIN AgrAgreement A
			ON A.PropNumber = Stg.Prop_No
	

	-- Break out the PS Code from the performance Site 
	UPDATE Stg_IE_SvcEntityFacility_Ctl SET PSCode = (SUBSTRING(sPerformanceSite,1,2))


	-- Now map the AdmFacility
	UPDATE STG	SET STG.AdmFacilityId = A.Id
		FROM Stg_IE_SvcEntityFacility_Ctl STG 
		INNER JOIN AdmFacility A
			ON A.PSCode = Stg.PSCode
	
	
	SET NOCOUNT OFF

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO